package com.wanmait.hotelManage.dao;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.List;

import com.wanmait.hotelManage.util.DBHelper;
import com.wanmait.hotelManage.vo.NewsComment;
import com.wanmait.hotelManage.vo.NewsCommentReport;
import com.wanmait.hotelManage.vo.Pager;
import com.wanmait.hotelManage.vo.Userinfo;

public class NewsCommentReportDAO {
	private NewsCommentReportDAO() {};
	private static NewsCommentReportDAO newsCommentReportDAO=new NewsCommentReportDAO();
	public static NewsCommentReportDAO getNewsCommentReportDAO() {
		return newsCommentReportDAO;
	}
	/**
	 * 新闻举报的findAll
	 * @param pager 
	 * @return
	 */
	public List<NewsCommentReport> findAll(Pager pager){
		List<NewsCommentReport>newsCommentReports=new ArrayList<NewsCommentReport>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT news_comment_report.userinfo_id,news_comment_report.reason,news_comment_report.id,news_comment_report.news_comment_id,news_comment_report.input_time,news_comment_report.read,news_comment.enable,userinfo.username,userinfo.id,news_comment.id ");
		sb.append(" from news_comment_report,news_comment,userinfo ");
		sb.append(" WHERE news_comment_report.userinfo_id=userinfo.id AND news_comment_report.news_comment_id=news_comment.id AND news_comment.enable=1 AND news_comment_report.read=0 order by news_comment_report.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			NewsCommentReport ncr=null;
			Userinfo ui=null;
			NewsComment nc=null;
			while(rs.next()) {
				ncr=new NewsCommentReport();
				ncr.setId(rs.getInt("news_comment_report.id"));
				ncr.setInputTime(rs.getTimestamp("news_comment_report.input_time"));
				ncr.setReason(rs.getString("news_comment_report.reason"));
				ncr.setRead(rs.getBoolean("news_comment_report.read"));
				ui=new Userinfo();
				ui.setId(rs.getInt("userinfo.id"));
				ui.setUsername(rs.getString("userinfo.username"));
				ncr.setUserinfo(ui);
				nc=new NewsComment();
				nc.setId(rs.getInt("news_comment.id"));
				nc.setEnable(rs.getBoolean("news_comment.enable"));
				ncr.setNewsComment(nc);
				newsCommentReports.add(ncr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return newsCommentReports;
	}
	/**
	 * 总行数
	 * @return
	 */
	public int getrows(){
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from news_comment_report,news_comment where news_comment.enable=1 and news_comment_report.read=0 and news_comment_report.news_comment_id=news_comment.id ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		String ids="1,2,3";
//		List<Integer>A=NewsCommentReportDAO.getNewsCommentReportDAO().findNewsCommentId(ids);
//		for (Integer integer : A) {
//			System.out.println(integer);
//		}
//	}
	/**
	 * 打回
	 * @param id
	 */
	public void de(Integer id) {
		String sql="update news_comment_report set news_comment_report.read=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	/**
	 * 通过
	 * @param id
	 */
	public void up(Integer id) {
		String sql="update news_comment_report set news_comment_report.enable=0,news_comment_report.read=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	/**
	 * 批量退回
	 * @param ids
	 */
	public void del(String ids) {
		String sql="update news_comment_report set news_comment_report.read=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 批量通过
	 * @param ids
	 */
	public void ups(String ids) {
		String sql="update news_comment_report set news_comment_report.enable=0,news_comment_report.read=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
	/**
	 * 获取NewsCommentId的值
	 * @param ids
	 * @return
	 */
	public List<Integer> findNewsCommentId(String ids) {
		List<Integer>int1=new ArrayList<Integer>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		String sql="SELECT news_comment_report.news_comment_id FROM news_comment_report WHERE id IN("+ids+")";
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sql);
			rs=ps.executeQuery();
			while(rs.next()) {
				int1.add(rs.getInt("news_comment_report.news_comment_id"));
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return int1;
	}
	public int getrowss(String text) {
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" select count(*) c from news_comment_report,news_comment,userinfo where news_comment.enable=1 and news_comment_report.read=0 and news_comment_report.userinfo_id=userinfo.id and userinfo.username like '%"+text+"%' and news_comment_report.news_comment_id=news_comment.id ");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			rs=ps.executeQuery();
			rs.next();
			Integer count=rs.getInt("c");
			return count;
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return 0;
	}
//	public static void main(String[] args) {
//		int rows=NewsCommentReportDAO.getNewsCommentReportDAO().getrowss("c");
//		System.out.println(rows);
//	}
	public List<NewsCommentReport> findname(String text, Pager pager) {
		List<NewsCommentReport>newsCommentReports=new ArrayList<NewsCommentReport>();
		Connection conn=null;
		PreparedStatement ps=null;
		ResultSet rs=null;
		StringBuilder sb=new StringBuilder();
		sb.append(" SELECT news_comment_report.userinfo_id,news_comment_report.reason,news_comment_report.id,news_comment_report.news_comment_id,news_comment_report.input_time,news_comment_report.read,news_comment.enable,userinfo.username,userinfo.id,news_comment.id ");
		sb.append(" from news_comment_report,news_comment,userinfo ");
		sb.append(" WHERE news_comment_report.userinfo_id=userinfo.id and userinfo.username like '%"+text+"%' AND news_comment_report.news_comment_id=news_comment.id AND news_comment.enable=1 AND news_comment_report.read=0 order by news_comment_report.input_time desc limit ?,?");
		conn=DBHelper.getDBHelper().getConn();
		try {
			ps=conn.prepareStatement(sb.toString());
			ps.setInt(1, pager.getStart());
			ps.setInt(2, pager.getPageSize());
			rs=ps.executeQuery();
			NewsCommentReport ncr=null;
			Userinfo ui=null;
			NewsComment nc=null;
			while(rs.next()) {
				ncr=new NewsCommentReport();
				ncr.setId(rs.getInt("news_comment_report.id"));
				ncr.setReason(rs.getString("news_comment_report.reason"));
				ncr.setInputTime(rs.getTimestamp("news_comment_report.input_time"));
				ncr.setRead(rs.getBoolean("news_comment_report.read"));
				ui=new Userinfo();
				ui.setId(rs.getInt("userinfo.id"));
				ui.setUsername(rs.getString("userinfo.username"));
				ncr.setUserinfo(ui);
				nc=new NewsComment();
				nc.setId(rs.getInt("news_comment.id"));
				nc.setEnable(rs.getBoolean("news_comment.enable"));
				ncr.setNewsComment(nc);
				newsCommentReports.add(ncr);
			}
		} catch (SQLException e) {
			// TODO Auto-generated catch block
			e.printStackTrace();
		}finally {
			DBHelper.getDBHelper().close(rs, ps, conn);
		}
		return newsCommentReports;
	}
	public void updateok(Integer id) {
		String sql="update news_comment_report set ok=1 where id=?";
		DBHelper.getDBHelper().executeUpdate(sql, id);
	}
	public void updateoks(String ids) {
		String sql="update news_comment_report set ok=1 where id in("+ids+")";
		DBHelper.getDBHelper().executeUpdate(sql);
	}
}
